package org.jeecg.lawcase.data.reader.impl;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecg.common.exception.JeecgBootException;
import org.jeecg.lawcase.data.reader.DataImportApadter;
import org.jeecg.lawcase.data.reader.DataImportManager;
import org.jeecg.lawcase.data.reader.DataTemplate;
import org.springframework.stereotype.Component;

import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Component
public class ExcelDataImportImpl implements DataImportApadter {

    private Workbook workbook = null;
    private String fileName = null;
    private String suffix = null;
    private InputStream inputStream = null;
    private Sheet sheet = null;
    private FormulaEvaluator evaluator = null;

    public ExcelDataImportImpl() {
        DataImportManager.registerService(this.getFileType(), this);
    }

    @Override
    public String getFileType() {
        return "xls,xlsx";
    }

    @Override
    public DataTemplate importData(String fileName, InputStream inputStream) {
        DataTemplate dataTemplate = new DataTemplate();
        try{
            this.inputStream = inputStream;
            if(fileName.toLowerCase().endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                workbook = new HSSFWorkbook(inputStream);
            }
            this.workbook.setForceFormulaRecalculation(true);
            this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            this.sheet = workbook.getSheetAt(0);
            this.sheet.setForceFormulaRecalculation(true);
            this.parseSheet(dataTemplate, 0);
        }catch (Exception e){
            throw new JeecgBootException(e);
        } finally {
            this.close();
        }
        return dataTemplate;
    }

    /**
     * 解析excel内容
     * @param startRow
     */
    public void parseSheet(DataTemplate dataTemplate, int startRow) {
        int rowStart = sheet.getFirstRowNum() + startRow;
        int rowEnd = sheet.getLastRowNum();
        int rowIndex = 0;
        for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) {
            Row r = sheet.getRow(rowNum);
            Cell cell = r.getCell(0);
            if (cell == null || getCellValue(cell).equals("")){
                break;
            }
            if(rowIndex == 0){
                this.setHeader(dataTemplate, r, cell);
            } else {
                this.addRow(dataTemplate, r, cell);
            }
            rowIndex ++;
        }
    }

    /**
     * 读取文件头
     * @param dataTemplate
     * @param row
     * @param cell
     */
    private void setHeader(DataTemplate dataTemplate, Row row, Cell cell){
        // 创建对象
        for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum ++) {
            // 列坐标
            CellReference cellRef = new CellReference(row.getRowNum(), cellNum);
            String cellValue = getCellValue(cell);
            dataTemplate.getHeaders().add(cellValue);
        }
    }

    /**
     * 读取数据内容
     * @param dataTemplate
     * @param row
     * @param cell
     */
    private void addRow(DataTemplate dataTemplate, Row row, Cell cell){
        List<String> data = new ArrayList<>();
        // 创建对象
        for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum ++) {
            // 列坐标
            CellReference cellRef = new CellReference(row.getRowNum(), cellNum);
            String cellValue = getCellValue(cell);
            data.add(cellValue);
        }
        dataTemplate.getDatas().add(data);
    }

    /**
     * 获取cell中的值并返回String类型
     *
     * @param cell
     * @return String类型的cell值
     */
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (null == cell) {
            return cellValue;
        }

        if(CellType.NUMERIC == cell.getCellType()){
            // 判断单元格的类型是否则NUMERIC类型
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                // 判断是否为日期类型
                Date date = cell.getDateCellValue();
                DateFormat formater = new SimpleDateFormat("yyyy/MM/dd");
                cellValue = formater.format(date);
            } else {
                DecimalFormat df = new DecimalFormat("####.####");
                cellValue = df.format(cell.getNumericCellValue());
            }
        } else if(CellType.STRING == cell.getCellType()){
            // 字符串
            cellValue = cell.getStringCellValue();
        } else if(CellType.BOOLEAN == cell.getCellType()){
            cellValue = cell.getBooleanCellValue() + "";
        } else if(CellType.FORMULA == cell.getCellType()){
            try {
                cellValue = String.valueOf(cell.getStringCellValue());
            } catch (IllegalStateException e) {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 判断是否为日期类型
                    Date date = cell.getDateCellValue();
                    DateFormat formater = new SimpleDateFormat("yyyy/MM/dd");
                    cellValue = formater.format(date);
                } else {
                    FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper()
                            .createFormulaEvaluator();
                    evaluator.evaluateFormulaCell(cell);
                    DecimalFormat df = new DecimalFormat("####.####");
                    cellValue = df.format(cell.getNumericCellValue());
                }
            }
        } else if(CellType.BLANK == cell.getCellType()){
            // 空值
            cellValue = "";
        } else if(CellType.ERROR == cell.getCellType()){
            // 空值
            cellValue = "";
        }
        return cellValue;
    }

    public void close() {
        if(workbook != null) {
            try {
                workbook.close();
            } catch (IOException e) {}
        }

        if(inputStream != null) {
            try {
                inputStream.close();
            } catch (IOException e) {}
        }
    }
}
